﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Windows.Forms;

/// <summary>
///Will contain all the database Operation
/// </summary>
/// 

public class DataAccessLayer
{

    public static string Connectionstring = ConfigurationManager.ConnectionStrings["STDB"].ConnectionString;

    #region Validation of User
    public static int ValidateUserCredentials(string username, string password)
    {
        #region validate user credentials
        int Isvalid = -1; //by default the value will be -1
        using (SqlConnection con = new SqlConnection(Connectionstring))
        {

            try
            {
                SqlCommand cmd = new SqlCommand("spAuthenticateUser", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Username", username);
                cmd.Parameters.AddWithValue("@Password", password);
                con.Open();
                Isvalid = Convert.ToInt32(cmd.ExecuteScalar());
                return Isvalid;

            }
            catch (Exception)
            {

                return Isvalid;
            }
        }
        #endregion
    }
    #endregion

    #region Creation of Users
    public static string CreateUser(Users_details Creation)
    {
        #region Create user credentials
        try
        {
            using (SqlConnection con = new SqlConnection(Connectionstring))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("sp_users", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Userid", Creation.UserID);
                cmd.Parameters.AddWithValue("@UserName", Creation.UserName);
                cmd.Parameters.AddWithValue("@UserPassword", Creation.UserPassword);
                cmd.Parameters.AddWithValue("@Email", Creation.Email);
                cmd.Parameters.AddWithValue("@RateType", Creation.RateType);
                cmd.Parameters.AddWithValue("@RegisteredDate", DateTime.Now);
                cmd.ExecuteNonQuery();

                return "Successfull";
            }
        }
        catch (Exception)
        {

            return "Not_Succsesfull";
        }
        #endregion

    }
    #endregion

    #region Getting Rates of all Products
    public static List<user_rates> getRates()
    {

        string qury = "Select RateId,OriginalRate,StandardRate,NormalRate,NormalRate,ProdunctId,ProductName,DateModified from Rates";
        List<user_rates> urate = new List<user_rates>();
        using (SqlConnection con = new SqlConnection(Connectionstring))
        {
            con.Open();
            SqlCommand cmd = new SqlCommand(qury);
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                user_rates ur = new user_rates();
                ur.RateId = rdr.GetValue(0).ToString();
                ur.OriginalRate = rdr.GetValue(1).ToString();
                ur.StandardRate = rdr.GetValue(2).ToString();
                ur.NormalRate = rdr.GetValue(3).ToString();
                ur.ProductId = rdr.GetValue(4).ToString();
                ur.ProductName = rdr.GetValue(5).ToString();
                ur.DateModified = Convert.ToDateTime(rdr.GetValue(6));
                urate.Add(ur);
            }

        }
        return urate;
    }

    #endregion

    #region Method for binding the productid and Productname to the combox

    public static DataTable Bind_ProductName_ProductId()
    {

        string qury = "Select ProductName,productid from products";
        using (SqlConnection con = new SqlConnection(Connectionstring))
        {

            con.Open();
            SqlCommand cmd = new SqlCommand(qury, con);
            DataTable ds = new DataTable();
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            adp.Fill(ds);
            return ds;


        }

    }


    #endregion

    #region Method for geting the Purchasedrate of selected product for addrate form

   
    public static string Return_PurchasedRate(string Productid)
    {

        using (SqlConnection conn = new SqlConnection(Connectionstring))
        {
            using (SqlCommand cmd = new SqlCommand("sp_Purchasedrate_From_Products_for_Rates", conn))
            {
                conn.Open();
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Productid", Productid);
                string Purchasedrate =Convert.ToString(cmd.ExecuteScalar());
                conn.Close();
                return Purchasedrate;
            }

        }
    }
    #endregion

    #region Save Rates
    public static string InsertRateDetails(user_rates rate)
    {

        try
        {
            using (SqlConnection conn = new SqlConnection(Connectionstring))
            {
                using (SqlCommand cmd = new SqlCommand("sp_SaveRates", conn))
                {
                    conn.Open();
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@RateID", rate.RateId);
                    cmd.Parameters.AddWithValue("@OriginalRate", rate.OriginalRate);
                    cmd.Parameters.AddWithValue("@StandardRate", rate.StandardRate);
                    cmd.Parameters.AddWithValue("@NormalRate", rate.NormalRate);
                    cmd.Parameters.AddWithValue("@ProductName", rate.ProductName);
                    cmd.Parameters.AddWithValue("@ProductID", rate.ProductId);
                    cmd.Parameters.AddWithValue("@DateModified", rate.DateModified);
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    return "Saved Successfully";
                }

            }
        }
        catch 
        {

            return "Saving Unsuccessful";
        }
    }

    #endregion

    #region Geting Product details
    public static List<SaveProduct> GetProductDetails()
    {

        string q = "select ProductID, ReceivedDate, ExpiryDate, Type, Product, Quantity from Products";
        List<SaveProduct> productlist = new List<SaveProduct>();


        using (SqlConnection conn = new SqlConnection(Connectionstring))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(q, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                SaveProduct pro = new SaveProduct();
                pro.ProductID = reader.GetValue(0).ToString();
                pro.ReceivedDate = Convert.ToDateTime(reader.GetValue(1));
                pro.ExpiryDate = Convert.ToDateTime(reader.GetValue(2));
                pro.Type = reader.GetValue(3).ToString();
                pro.ProductName = reader.GetValue(4).ToString();
                pro.Quantity = reader.GetValue(5).ToString();
                productlist.Add(pro);
            }

        }
        return productlist;
    }
    #endregion

    #region Save Product details
    public static string Saveproductdetial(SaveProduct details)
    {

        using (SqlConnection con = new SqlConnection(Connectionstring))
        {
            try
            {
                SqlCommand cmd = new SqlCommand("sp_products", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();

                cmd.Parameters.AddWithValue("@ProductId", details.ProductID);
                cmd.Parameters.AddWithValue("@ReceivedDate", details.ReceivedDate);
                cmd.Parameters.AddWithValue("@ExpiryDate", details.ExpiryDate);
                cmd.Parameters.AddWithValue("@Type", details.Type);
                cmd.Parameters.AddWithValue("@PurchasedRate", details.PurchasedRate);
                cmd.Parameters.AddWithValue("@Company", details.Company);
                cmd.Parameters.AddWithValue("@ProductName", details.ProductName);
                cmd.Parameters.AddWithValue("@Quantity", details.Quantity);
                cmd.Parameters.AddWithValue("@CreditorsName", details.CreditorsName);
                cmd.Parameters.AddWithValue("@Description", details.Description);
                cmd.Parameters.AddWithValue("@Command", "Insert");

                cmd.ExecuteNonQuery();
                con.Close();
                return "Saved Successfully";


            }
            catch (Exception)
            {
                return "Saving Unsuccessful";
            }


        }

    }
    #endregion

    #region Update ProductDetails
    public static string updateProduct(SaveProduct prop)
    {

        using (SqlConnection conn = new SqlConnection(Connectionstring))
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand("sp_products", conn))
                {
                    conn.Open();
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ProductID", prop.ProductID);
                    cmd.Parameters.AddWithValue("@ProductName", prop.ProductName);
                    cmd.Parameters.AddWithValue("@Quantity", prop.Quantity);
                    cmd.Parameters.AddWithValue("@Description", prop.Description);

                    cmd.Parameters.AddWithValue("@ReceivedDate", DBNull.Value);
                    cmd.Parameters.AddWithValue("@ExpiryDate", DBNull.Value);
                    cmd.Parameters.AddWithValue("@Type", DBNull.Value);
                    cmd.Parameters.AddWithValue("@PurchasedRate", DBNull.Value);
                    cmd.Parameters.AddWithValue("@Company", DBNull.Value);


                    cmd.Parameters.AddWithValue("@CreditorsName", DBNull.Value);

                    cmd.Parameters.AddWithValue("@Command", "Update");
                    cmd.ExecuteNonQuery();













                    return "Updated";
                }
            }
            catch (Exception)
            {
                return "Unsuccessfull";

            }
        }


    }

    #endregion


}



